from fastapi import APIRouter, Request, Depends, HTTPException
from fastapi.responses import HTMLResponse
from fastapi.templating import Jinja2Templates
import aiohttp
import asyncio
from datetime import datetime, timedelta
from sqlalchemy.orm import sessionmaker
from models import PredictionRecord, DailyRecord, Base

# 创建数据库连接
from sqlalchemy import create_engine
from config import settings
from data_processing import process_api_data
import platform
from sklearn.linear_model import LinearRegression
import numpy as np

router = APIRouter()
templates = Jinja2Templates(directory="templates")

engine = create_engine("sqlite:///predictions.db")
Base.metadata.create_all(engine)  # 这行会创建所有表
DBSession = sessionmaker(bind=engine)


async def get_session():
    async with aiohttp.ClientSession() as session:
        yield session


# 设置token（你可以设置任何你想要的值）
ACCESS_TOKEN = "tayun123!"


@router.get("/", response_class=HTMLResponse)
async def read_root(
    request: Request,
    session: aiohttp.ClientSession = Depends(get_session),
):
    # 判断是否是Mac环境
    is_mac = platform.system() == "Darwin"

    # 从headers中获取token
    token = request.headers.get("Authorization")

    # 如果不是Mac环境，必须验证token
    if not is_mac and (not token or token != ACCESS_TOKEN):
        raise HTTPException(status_code=401, detail="未授权访问")

    try:
        # 获取产品数据
        tasks = [
            process_api_data(session, product, config)
            for product, config in settings.PRODUCTS.items()
        ]
        products_data_lists = await asyncio.gather(*tasks)
        all_products_data = [
            item for sublist in products_data_lists for item in sublist
        ]

        # 添加调试输
        # print("Products data:", all_products_data)

        # 即使没有数据也继续渲染页面
        total_predicted_sales = sum(
            p.get("predicted_sales", 0) for p in all_products_data
        )

        # 保存预测记录
        db_session = DBSession()
        new_record = PredictionRecord(predicted_value=total_predicted_sales)
        db_session.add(new_record)
        db_session.commit()

        # 获取当前时间（移到最前面）
        current_time = datetime.now()
        current_minutes = current_time.hour * 60 + current_time.minute

        # 获取今天和昨天的日期
        today = datetime.now().date()
        yesterday = today - timedelta(days=1)

        # 获取今天的记录
        db_session = DBSession()
        today_records = (
            db_session.query(PredictionRecord)
            .filter(
                PredictionRecord.timestamp >= today,
                PredictionRecord.timestamp <= current_time,
            )
            .order_by(PredictionRecord.timestamp)
            .all()
        )

        # 转换今天的记录为预测点
        prediction_points = []
        for record in today_records:
            point = {
                "timestamp": record.timestamp.strftime("%H:%M"),
                "value": record.predicted_value,
                "record_id": record.id,  # 保留record_id用于后续异常点处理
            }
            prediction_points.append(point)

        # 获取昨天的记录
        yesterday_records = (
            db_session.query(PredictionRecord)
            .filter(
                PredictionRecord.timestamp >= yesterday,
                PredictionRecord.timestamp < today,
            )
            .order_by(PredictionRecord.timestamp)
            .all()
        )

        # 获取历史数据用于趋势图
        historical_end_date = yesterday  # 截止到昨日
        historical_start_date = historical_end_date - timedelta(days=29)  # 默认30天

        historical_records = (
            db_session.query(DailyRecord)
            .filter(
                DailyRecord.date_str >= historical_start_date.strftime("%Y-%m-%d"),
                DailyRecord.date_str <= historical_end_date.strftime("%Y-%m-%d"),
            )
            .order_by(DailyRecord.date_str)
            .all()
        )

        # 组织历史数据
        historical_data_by_date = {}
        historical_products = set()

        for record in historical_records:
            date_str = record.date_str
            product = record.product_name
            historical_products.add(product)

            if date_str not in historical_data_by_date:
                historical_data_by_date[date_str] = {}
            historical_data_by_date[date_str][product] = record.order_sum

        # 准备历史数据结构
        historical_dates = sorted(historical_data_by_date.keys())
        historical_chart_data = {
            "dates": historical_dates,
            "products": sorted(list(historical_products)),
            "data": {},
        }

        # 为每个产品构建数据系列
        for product in historical_chart_data["products"]:
            historical_chart_data["data"][product] = []
            for date_str in historical_dates:
                value = historical_data_by_date.get(date_str, {}).get(product, 0)
                historical_chart_data["data"][product].append(value)

        # 计算总计数据
        historical_chart_data["data"]["总计"] = []
        for date_str in historical_dates:
            total = sum(historical_data_by_date.get(date_str, {}).values())
            historical_chart_data["data"]["总计"].append(total)

        # 转换昨天的记录为预测点
        yesterday_points = []
        for record in yesterday_records:
            record_minutes = record.timestamp.hour * 60 + record.timestamp.minute
            # 将昨天的时间映射到今天对应的时间点
            mapped_time = current_time.replace(
                hour=record.timestamp.hour, minute=record.timestamp.minute
            ).strftime("%H:%M")

            point = {
                "timestamp": mapped_time,
                "value": record.predicted_value,
                "is_yesterday": True,
            }
            yesterday_points.append(point)

        # 确保按时间排序
        yesterday_points.sort(key=lambda x: x["timestamp"])

        # 添加调试输出
        print(f"当前时间: {current_time}, 当前分钟数: {current_minutes}")
        print(f"获取到今天的记录数量: {len(today_records)}")
        print(f"处理后的今天数据点数量: {len(prediction_points)}")
        print(f"获取到昨天的记录数量: {len(yesterday_records)}")
        print(f"处理后的昨天数据点数量: {len(yesterday_points)}")

        # 检测异常点并删除
        if len(prediction_points) >= 30:  # 确保有足够的点进行异常检测
            # 计算均值和标准差
            values = [p["value"] for p in prediction_points]
            mean_value = sum(values) / len(values)
            std_dev = (sum((x - mean_value) ** 2 for x in values) / len(values)) ** 0.5

            # 方案1：增加标准差的倍数
            std_threshold = 3  # 改为3或更大，这样会放宽异常点的判定范围

            # 方案2：使用百分比变化来判断异常
            percent_threshold = 0.18  # 允许30%的波动
            upper_bound = mean_value * (1 + percent_threshold)
            lower_bound = mean_value * (1 - percent_threshold)

            # 或者方案3：结合两种方法，取较大的范围
            std_upper_bound = mean_value + std_threshold * std_dev
            std_lower_bound = mean_value - std_threshold * std_dev
            percent_upper_bound = mean_value * (1 + percent_threshold)
            percent_lower_bound = mean_value * (1 - percent_threshold)

            upper_bound = max(std_upper_bound, percent_upper_bound)
            lower_bound = min(std_lower_bound, percent_lower_bound)

            # 打印统计信息
            print(f"均值: {mean_value}")
            print(f"标准差: {std_dev}")
            print(f"上限: {upper_bound}")
            print(f"下限: {lower_bound}")

            # 找出并打印异常点
            outliers = [
                point
                for point in prediction_points
                if not (lower_bound <= point["value"] <= upper_bound)
            ]

            if outliers:
                print("\n发生异常点:")
                for outlier in outliers:
                    print(f"时间: {outlier['timestamp']}, 值: {outlier['value']}")

            # 找出异常点的ID
            outlier_ids = [
                point["record_id"]
                for point in prediction_points
                if not (lower_bound <= point["value"] <= upper_bound)
            ]

            # 从数据库中删除异常点
            if outlier_ids:
                print(f"删除异常点ID: {outlier_ids}")
                db_session.query(PredictionRecord).filter(
                    PredictionRecord.id.in_(outlier_ids)
                ).delete(synchronize_session=False)
                db_session.commit()

                # 重新获取清理后的记录
                prediction_points = [
                    point
                    for point in prediction_points
                    if point["record_id"] not in outlier_ids
                ]

        # 生成未来预测点
        if prediction_points:
            future_points = generate_future_predictions(prediction_points)
            prediction_points.extend(future_points)

        # 移除record_id，因为前端不需要这个字段
        for point in prediction_points:
            point.pop("record_id", None)

        db_session.close()

        # 计算y轴的最大最小值
        all_values = [point["value"] for point in prediction_points]
        if all_values:
            max_value = (
                max(max(all_values), settings.CHART_SETTINGS.TARGET_VALUE) + 2000
            )
            min_value = (
                min(min(all_values), settings.CHART_SETTINGS.WARNING_VALUE) - 2000
            )
        else:
            max_value = settings.CHART_SETTINGS.TARGET_VALUE + 2000
            min_value = settings.CHART_SETTINGS.WARNING_VALUE - 2000

        # 生成回归预测线
        regression_points = generate_regression_line(prediction_points)

        # 在返回模板之前添加调试输出
        print("昨日数据点示例:")
        if yesterday_points:
            print(f"第一个点: {yesterday_points[0]}")
            print(f"最后一个点: {yesterday_points[-1]}")
            print(f"总点数: {len(yesterday_points)}")
        else:
            print("没有昨日数据点")

        # 计算昨日销售额总和
        total_yesterday_sales = sum(
            p.get("last_order_sum", 0) for p in all_products_data
        )

        # 计算总的近7日日均流水作为新的预警线
        total_7days_avg_revenue = sum(
            p.get("recent_7days_avg_revenue", 0) for p in all_products_data
        )

        # 确保返回的数据中包含 regression_points
        return templates.TemplateResponse(
            "report.html",
            {
                "request": request,
                "products_data": all_products_data,
                "prediction_points": prediction_points,
                "yesterday_points": yesterday_points,  # 确保这个变量被传递
                "regression_points": regression_points or [],
                "config": settings,
                "chart_bounds": {"max": max_value, "min": min_value - 2000},
                "total_yesterday_sales": total_yesterday_sales,  # 添加昨日销售额总和
                "total_7days_avg_revenue": total_7days_avg_revenue,  # 添加近7日日均流水总和
                "historical_chart_data": historical_chart_data,  # 添加历史数据
                "error_message": "" if all_products_data else "无法获取产品数据",
            },
        )

    except Exception as e:
        print(f"Error in read_root: {e}")
        return templates.TemplateResponse(
            "report.html",
            {
                "request": request,
                "products_data": [],
                "prediction_points": [],
                "regression_points": [],  # 确保错误情况下也有这个值
                "config": settings,
                "chart_bounds": {
                    "max": settings.CHART_SETTINGS.TARGET_VALUE,
                    "min": settings.CHART_SETTINGS.WARNING_VALUE,
                },
                "total_7days_avg_revenue": 0,  # 错误情况下也提供默认值
                "error_message": f"发生错误: {str(e)}",
            },
        )


@router.get("/duanju", response_class=HTMLResponse)
async def read_duanju(
    request: Request, session: aiohttp.ClientSession = Depends(get_session)
):
    return templates.TemplateResponse("duanju.html", {"request": request})


def generate_future_predictions(prediction_points):
    """使用线性回归生成趋势预测线"""
    now = datetime.now()

    # 只使用最近3.5小时来进行模拟
    two_hours_ago = now - timedelta(hours=1.5)
    recent_points = []

    for point in prediction_points:
        if point.get("is_future"):
            continue
        hour, minute = map(int, point["timestamp"].split(":"))
        point_time = now.replace(hour=hour, minute=minute)
        if point_time >= two_hours_ago:
            recent_points.append(point)

    if len(recent_points) < 4:  # 确保有足够的点进行回归
        print("Not enough recent points for regression")
        return []

    # 准备回归数据
    X = []  # 时间转换为分钟数
    y = []  # 实际值
    base_minute = now.hour * 60 + now.minute

    for point in recent_points:
        hour, minute = map(int, point["timestamp"].split(":"))
        minutes = hour * 60 + minute - base_minute  # 相对于当前时间的分钟数
        X.append([minutes])
        y.append(point["value"])

    # 训练线性回归模型
    model = LinearRegression()
    model.fit(X, y)

    # 获取最后一个实际数据点的值作为起点
    last_value = recent_points[-1]["value"]

    # 生成未来预测点（使用线性回归的预测值）
    future_points = []
    current_hour = now.hour
    current_minute = now.minute

    # 预测到当天结束
    for hour in range(current_hour, 24):
        start_minute = current_minute if hour == current_hour else 0

        for minute in range(start_minute, 60, 15):
            if hour == current_hour and minute <= current_minute:
                continue

            # 计算相对于当前时间的分钟数
            minutes = (hour * 60 + minute) - base_minute

            # 使用模型预测值
            predicted_value = model.predict([[minutes]])[0]

            future_points.append(
                {
                    "timestamp": f"{hour:02d}:{minute:02d}",
                    "value": max(0, round(predicted_value)),
                    "is_future": True,
                }
            )

    print(f"Generated {len(future_points)} future points with linear regression")
    return future_points


def generate_regression_line(prediction_points):
    """生成加权线性回归预测线"""
    now = datetime.now()

    # 只使用最近4小时的实际数据点进行回归
    four_hours_ago = now - timedelta(hours=6)
    actual_points = []

    for point in prediction_points:
        if point.get("is_future"):
            continue
        hour, minute = map(int, point["timestamp"].split(":"))
        point_time = now.replace(hour=hour, minute=minute)
        if point_time >= four_hours_ago:
            actual_points.append(point)

    if len(actual_points) < 6:  # 确保有足够的点进行回归
        return []

    # 准备训练数
    X = []  # 时间转换为分钟数
    y = []  # 实际值
    weights = []  # 权重数组

    # 计算最近时间点
    latest_time = max(
        int(p["timestamp"].split(":")[0]) * 60 + int(p["timestamp"].split(":")[1])
        for p in actual_points
    )

    for point in actual_points:
        hour, minute = map(int, point["timestamp"].split(":"))
        minutes = hour * 60 + minute
        X.append([minutes])
        y.append(point["value"])

        # 计算权重 - 越近的点权重越大
        time_diff = latest_time - minutes
        weight = np.exp(-time_diff / 120)  # 使用指数衰减
        weights.append(weight)

    # 训练加权线性回归模型
    model = LinearRegression()
    model.fit(X, y, sample_weight=weights)

    # 生成预测线点
    regression_points = []
    current_minutes = now.hour * 60 + now.minute

    # 生成从当前时间开始的预测点
    for hour in range(now.hour, min(24, now.hour + 5)):  # 预测未来4-5小时
        for minute in range(0, 60, 15):  # 每15分钟一个点
            minutes = hour * 60 + minute
            if minutes >= current_minutes:
                predicted_value = model.predict([[minutes]])[0]
                regression_points.append(
                    {
                        "timestamp": f"{hour:02d}:{minute:02d}",
                        "value": max(0, round(predicted_value)),
                        "is_regression": True,
                    }
                )

    return regression_points


def align_yesterday_points(yesterday_records, current_time):
    """处理昨日数据点，保持原始时间范围"""
    aligned_points = []

    for record in yesterday_records:
        point = {
            "timestamp": record.timestamp.strftime("%H:%M"),
            "value": record.predicted_value,
            "is_yesterday": True,
        }
        aligned_points.append(point)

    # 确保按时间排序
    aligned_points.sort(key=lambda x: x["timestamp"])

    return aligned_points
